Imports System.Data.SqlClient
Imports System.Text

Friend Class DBTableSize_Factory
    Inherits DBBase

    Public Function AverageWeeklyDatabaseSize( _
                        ByVal lCountOfWeeks As Long, _
                        ByVal lDatabaseID As Long) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageWeeklyDatabaseSize"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@CountOfWeeks", SqlDbType.Int)
        oParm.Value = lCountOfWeeks
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function AverageWeeklyTableSize( _
                        ByVal lCountOfWeeks As Long, _
                        ByVal lTableID As Long, _
                        ByVal sEndingWeek As String) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageWeeklyTableSize"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@CountOfWeeks", SqlDbType.Int)
        oParm.Value = lCountOfWeeks
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@TableID", SqlDbType.BigInt)
        oParm.Value = lTableID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@EndingWeek", SqlDbType.DateTime)
        oParm.Value = sEndingWeek
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function TableAverageSizesForWeek( _
                        ByVal sWeek As String, _
                        ByVal lDatabaseID As Long) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_TableSize10PointTrends"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@StartDate", SqlDbType.DateTime)
        oParm.Value = DateAdd(DateInterval.WeekOfYear, -10, CDate(sWeek))
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@EndDate", SqlDbType.DateTime)
        oParm.Value = sWeek
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Sub RemoveExpiredData(ByVal iExpirationPeriod As Integer)
        Dim sSQL As New StringBuilder("delete from dbo.TableSizeHistory")
        sSQL.Append(" where")
        sSQL.Append("   datediff(day,[CreateDate],current_timestamp)>" & iExpirationPeriod)

        MyBase.ExecuteNonQuery(sSQL.ToString)
    End Sub

    Public Sub RemoveSessionData(ByVal lSessionID As Long)
        Dim sSQL As New StringBuilder("delete from dbo.TableSizeHistory")
        sSQL.Append(" where")
        sSQL.Append("   [Session]=" & lSessionID)

        MyBase.ExecuteNonQuery(sSQL.ToString)
    End Sub
End Class
